<?php

namespace app\admin\controller;

use app\common\controller\AdminBaseController;
use org\util\Baksql;
use org\util\DbHelper;
use think\Db;
use think\facade\Debug;

class DatabaseController extends AdminBaseController {
    /**
     * 需要鉴权的方法,但需要登录
     * @var array
     */
    protected $needRight = [];
    /**
     * 初始化方法
     */
    public function __construct() {
        parent::__construct();
        $this->model = new Baksql();
    }

    /**
     * 后台首页
     */
    public function index() {
        $filelist = $this->model->filelist();
        $this->assign('filelist',$filelist);
        return $this->fetch();
    }

    /**
     * 备份数据库
     */
    public function backup() {
        try {
            $res = $this->model->backup();
        } catch (\Exception $e) {
            $this->error($e->getMessage());
        }
        $this->success($res);
    }

    /**
     * 还原数据库
     */
    public function restore() {
        try {
            $fileName = $this->request->param('file_name');
            $res = $this->model->restore($fileName);
        } catch (\Exception $e) {
            $this->error($e->getMessage());
        }
        $this->success($res);
    }

    /**
     * 下载备份文件
     */
    public function download() {
        try{
            $fileName = $this->request->param('file_name');
            $this->model->downloadFile($fileName);
        }catch (\Exception $e){
            $this->error($e->getMessage());
        }
        $this->success('操作成功');
    }

    /**
     * 删除备份文件
     */
    public function del(){
        try{
            $fileName = $this->request->param('file_name');
            $this->model->delfile($fileName);
        }catch (\Exception $e){
            $this->error($e->getMessage());
        }
        $this->success('操作成功');
    }

    /**
     * 字段替换
     * @return mixed
     */
    public function replace(){
        if($this->request->isAjax()){
            try{
                $data = $this->request->param();
                $rule=[
                    'table_name|表名'=>'require',
                    'field_name|字段'=>'require',
                    'replace_type|替换模式'=>'require|number',
                ];
                if($data['replace_type']==1){
                    $rule['old_str|待替换字符']='require';
                }
                if($data['replace_type']==2){
                    $rule['old_start_str|字符开始']='require';
                    $rule['old_end_str|字符结束']='require';
                }
                $this->validate($data,$rule);
                //查询条件过滤
                $query = addslashes($data['query']);
                $query=!empty($query) ? ' where '.$query : '';
                if($data['replace_type']==1){
                    $sql = "UPDATE `{$data['table_name']}` 
                        SET `{$data['field_name']}` = replace (`{$data['field_name']}`,'{$data['old_str']}','{$data['new_str']}') {$query}";
                }
                if($data['replace_type']==2){
                    $len = mb_strlen($data['old_start_str'],'utf-8'); //开始字符串长度
                    $sql = "UPDATE `{$data['table_name']}` 
                        SET `{$data['field_name']}` = replace(
                                  `{$data['field_name']}`, 
                                  substring(
                                    `{$data['field_name']}`, 
                                    locate('{$data['old_start_str']}', `{$data['field_name']}`)+{$len},
                                    locate('{$data['old_end_str']}', `{$data['field_name']}`)-(locate('{$data['old_start_str']}', `{$data['field_name']}`)+{$len})
                                  ),
                                  '{$data['new_str']}'
                             ) {$query}";
                }
                if($data['replace_type']==3){
                    $sql = "UPDATE `{$data['table_name']}` SET `{$data['field_name']}` = '{$data['new_str']}' {$query}";
                }
                Db::execute($sql);
            }catch (\Exception $e){
                $this->error($e->getMessage());
            }
            $this->success('替换成功！',url('replace'));
        }
        $tables = DbHelper::getTables();
        $this->assign('tables',$tables);
        return $this->fetch();
    }

    /**
     * 获取对应表字段
     * @return mixed
     */
    public function fields(){
        $tableName = $this->request->param('table_name');
        $fields = DbHelper::getFields($tableName);
        return $fields;
    }

    /**
     * 执行sql
     * @return mixed
     */
    public function sql(){
        if($this->request->isAjax()){
            try{
                $sql = $this->request->param('sql');
                if(empty($sql)) exception('sql语句不能为空！');
                $sql = str_replace("\r", "", $sql);
                if (preg_match("/^(select|explain)(.*)/i ", $sql)) {
                    Debug::remark("begin");
                    $res = Db::query($sql);
                    Debug::remark("end");
                    $time = Debug::getRangeTime('begin', 'end', 4);
                    $data="<p class='text-danger'>共耗时{$time}s</p>";
                    if($res){
                        foreach ($res as $m => $n) {
                            foreach ($n as $k => $v) {
                                $data .= "<span class='text-primary'>{$k}：</span>{$v}<br/>\r\n";
                            }
                            $data .= "<hr style='color:red;' />";
                        }
                    }else{
                        $data .="<p class='text-primary'>查询结果为空</p>";
                    }

                }else{
                    Debug::remark("begin");
                    $res = Db::execute($sql);
                    Debug::remark("end");
                    $time = Debug::getRangeTime('begin', 'end', 4);
                    $data="<p class='text-danger'>共耗时{$time}s</p>";
                    $data .="<p class='text-primary'>{$res}行数据受影响</p>";
                }
            }catch (\Exception $e){
                $this->error($e->getMessage());
            }
            $this->success('操作成功！',null,$data);
        }
        return $this->fetch();
    }

    /**
     * 优化/修复表
     */
    public function repair(){
        if($this->request->isAjax()){
            try{
                $type = $this->request->param('type');
                $tableName = $this->request->param('table_name');
                if(empty($tableName) || empty($type)){
                    exception('参数不正确！');
                }
                $tableNames = explode(',',$tableName);
                foreach($tableNames as $table){
                    if($type=='optimize'){ //优化
                        Db::execute("OPTIMIZE TABLE `{$table}`");
                    }
                    if($type=='repair'){ //修复
                        Db::execute("REPAIR TABLE `{$table}`");
                    }
                }
            }catch (\Exception $e){
                $this->error($e->getMessage());
            }
            $this->success('操作成功！',url('repair'));
        }
        $tables = DbHelper::getTables();
        $this->assign('tables',$tables);
        return $this->fetch();
    }

    /**
     * 导出数据库文档
     * @return mixed
     */
    public function exporthtml(){
        $database = config('database.database');
        $tables = DbHelper::export();
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename=数据库设计文档--'.$database.'.html');
        $this->assign('database',$database);
        $this->assign('tables',$tables);
        return $this->fetch();
    }
}
